package com.yaojiaxun.logisticsPlatformSystem.pacong;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
 * @Auther: yjx_j
 * @Date: 2019/1/13 12:33
 * @Description:
 */

public class AddRelation
{
    private static Connection connection;

    public static void main(String[] args)
    {
        initDB();
        try
        {
            // 获取源表中一行数据
            ResultSet rs = getAllCitys();
            rs.next();
            while (rs.next())
            {
                // 如果该项存在父ID，则跳过设置
                if (rs.getInt("parent_id") == 0)
                {
                    List<String> parentCodes = getParentCodes(rs.getString("city_code"));
                    // 获取目标数据库的数据ID
                    int parentId = getParentId(parentCodes, rs.getInt("city_level") - 1);
                    doUpdate(rs.getInt("id"), parentId);
                    System.out.println("handling：" + rs.getInt("id"));
                }
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }

        closeDB();
    }

    private static void doUpdate(int id, int parentId)
    {
        try
        {
            Statement statement = connection.createStatement();
            statement.executeUpdate("UPDATE area SET parent_id = " + parentId + " WHERE id = "
                    + id);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

    private static int getParentId(List<String> parentCodes, int level) throws SQLException
    {
        Statement statement = connection.createStatement();
        for (String string : parentCodes)
        {
            ResultSet executeQuery = statement
                    .executeQuery("select * from area where city_code='" + string
                            + "' and city_level=" + level);
            if (executeQuery.next())
            {
                return executeQuery.getInt("id");
            }
        }
        return -1;
    }

    private static List<String> getParentCodes(String cityCode)
    {
        List<String> dataList = new ArrayList<String>();

        if (cityCode.endsWith("0"))
        {
            String code = rmvLastZero(cityCode);
            for (int i = 1; i < code.length() - 1; i++)
            {
                String substring = code.substring(0, code.length() - i);
                StringBuilder sb = new StringBuilder(substring);
                for (int j = substring.length(); j < 12; j++)
                {
                    sb.append("0");
                }
                dataList.add(sb.toString());
            }
        }
        else
        {
            for (int i = 1; i < cityCode.length() - 1; i++)
            {
                String substring = cityCode.substring(0, cityCode.length() - i);
                StringBuilder sb = new StringBuilder(substring);
                for (int j = 1; j <= i; j++)
                {
                    sb.append("0");
                }
                dataList.add(sb.toString());
            }
        }
        return dataList;
    }

    private static String rmvLastZero(String cityCode)
    {
        while (cityCode.endsWith("0"))
        {
            cityCode = cityCode.substring(0, cityCode.length() - 1);
        }
        return cityCode;
    }

    private static ResultSet getAllCitys()
    {
        try
        {
            Statement createStatement = connection.createStatement();
            return createStatement.executeQuery("select * from area");
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            return null;
        }
    }

    private static void closeDB()
    {
        if (connection != null)
        {
            try
            {
                connection.close();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
    }

    private static void initDB()
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager
                    .getConnection("jdbc:mysql://127.0.0.1:3306/LogisticsPlatform?characterEncoding=utf-8&useSSL=false", "root", "123456");
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
    }
}

